﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Transactions;
using PDO.DataServiceProvider.EntLibDataBlock;
using PDO.Utilities;

namespace PDO.DataPersistence.ServiceProvider
{
    /// MS + This is already single threaded.. Could [Synchronized] help out?
    /// <summary>
    /// Implementation of a IDataObjectSourceProvider connecting directly to a SQL database.
    /// </summary>
    public class DataObjectSourceDirect : IDataObjectSourceProvider
    {
        private SqlDatabase _sqlDB;
        private TransactionTracker _tranTracker;
        private int _transactionTimeout = 30;
        private object _sync;
		private bool _logExec = false;

        public DataObjectSourceDirect(string connectionStringKey)
        {
            Initialize();
            SetSqlConnection(connectionStringKey);
        }

        /// <summary>
        /// Sets the SQL connection object from the given connection builder object
        /// </summary>
        public DataObjectSourceDirect(SqlConnectionStringBuilder connectionStringBuilder)
        {
            Initialize();
            _sqlDB = new SqlDatabase(connectionStringBuilder.ConnectionString);
        }

        private void Initialize()
        {
            _sync = new object();
			_tranTracker = new TransactionTracker();

			try
			{
				string tmp = ConfigurationHelper.ReadConfigurationString("CIS.DataServiceProvider.TransactionTimeout", "30");
				int t = 0;
				if (int.TryParse(tmp, out t))
					_transactionTimeout = t;

				_tranTracker.DefaultTimeoutSeconds = _transactionTimeout;

				tmp = ConfigurationHelper.ReadConfigurationString("CIS.DataServiceProvider.LogExec", "0");
				t = 0;
				if (int.TryParse(tmp, out t))
					_logExec = (t==0 ? false : true);
			}
			catch
			{
				;
			}
        }

        /// <summary>
        /// Sets the current SQL connection object. The connection string for the connection is read from the entry assembly 
        /// app.config.
        /// </summary>
        /// <param name="connectionStringKey">The Name (or Key) of the SQL Connection string specified in the entry assembly app.config</param>
        private void SetSqlConnection(string connectionStringKey)
        {
            _sqlDB = new SqlDatabase(DatabaseConnectionBuilder.GetConnectionString(connectionStringKey));
        }

        SqlConnection GetConnection()
        {
            return _sqlDB.CreateConnection() as SqlConnection;
        }


        #region IDataObjectSourceProvider Members

        /// <summary>
        /// Simply try opening a connection.  
        /// </summary>
        /// <returns>true for success, false otherwise</returns>
        public bool TestConnection()
        {
            SqlConnection cn = _sqlDB.CreateConnection() as SqlConnection;	// GetConnection();

            try
            {
                cn.Open();
            }
            catch
            {
                return false;  // not interested in any exception details, just want a true/false return from this method.
            }
            finally
            {
                cn.Close();
            }
            return true;
        }


        #region Transactions

        // TEST TRANSACTIONS
        private void LogExec(string execkind, DataRequest dataRequest)
        {
//#if TRANSACTION_TEST
			if (!_logExec)
				return;

			bool errflg = false;
			string msg = "";
			msg += string.Format(DateTime.Now.ToString("HH:mm:ss.ff") + " {0} {1}", 
				execkind, 
				(dataRequest.SqlCommandString.Length <= 45 ? dataRequest.SqlCommandString : dataRequest.SqlCommandString.Substring(0, 45) + "..."));
			if (Transaction.Current != null)
			{
				Transaction t = Transaction.Current;
				msg += string.Format(" : Transaction {0} {1} {2}",
					t.TransactionInformation.CreationTime.ToString("HH:mm:ss.ff"), 
					gTrim(t.TransactionInformation.LocalIdentifier), 
					t.TransactionInformation.Status);

				if (Transaction.Current.TransactionInformation.Status != TransactionStatus.Active)
					errflg = true;
			}
			//lock(_tranTracker.consoleLock)
			{
				if (errflg == true)
					Console.ForegroundColor = ConsoleColor.Red;
				Console.WriteLine(msg);
				if (errflg == true)
					Console.ResetColor();
			}
//#endif
        }

//#if TRANSACTION_TEST
		private string gTrim(string guid)
		{
			if (guid.Length < 5)
				return guid;
			int x = guid.LastIndexOf(":");
			if (x >= 0)
				return guid.Substring(x - 4);
			return guid;
		}
//#endif
        // TEST TRANSACTIONS

        // All database operations pass through Windows Communication Foundation. Although WCF can be set up to pass transactions through, 
        // if the service endpoint is in another application domain (i.e. another machine), WCF immediately and unconditionally 
        // promotes the transaction to 'distributed' and involves Distributed Transaction Coordinator, with all its security setup headaches.
        // 
        // The base assumption here is that we are only performing transactions on one database at a time. 
        // It may be the local database or the remote database, but only one at a time.
        // 
        // In order to bypass DTC, and allow the top level application to control transaction scope 
        // (required in order to execute multiple database operations in a transaction), 
        // we have implemented communication methods for Begin/Commit/Rollback Transaction.
        // 
        // TransactionScope nests.
        // System.Transaction exists per thread.
        // Database transactions exist per database connection.

        public int TransactionTimeout
        {
            get
            {
                return _transactionTimeout;
            }
        }

        public void BeginTransaction(string transactionid)
        {
            _tranTracker.Add(transactionid);
        }

        public void CommitTransaction(string transactionid)
        {
            _tranTracker.Commit(transactionid);
        }

        public void RollbackTransaction(string transactionid)
        {
            _tranTracker.Rollback(transactionid);
        }

        #endregion


        public virtual System.Data.DataSet ExecuteDataSet(DataRequest dataRequest)
        {
            try
            {

//                Transaction.Current = _tranTracker.Find(dataRequest.TransactionID);
                LogExec("ExecuteDataSet", dataRequest);
                if (Transaction.Current != null && Transaction.Current.TransactionInformation.Status != TransactionStatus.Active)
                    throw new TransactionException("Execute not allowed on inactive transaction");

                if (null != dataRequest.Parameters)
                {
                    if (dataRequest.CommandType == CommandType.Text)
                    {
                        if (dataRequest.Parameters.Count > 0)
                        {
                            SqlParameter[] sqp = new SqlParameter[dataRequest.Parameters.Count];
                            for (int i = 0; i < dataRequest.Parameters.Count; i++)
                            {
                                sqp[i] = dataRequest.Parameters[i] as SqlParameter;
                            }
                            return _sqlDB.ExecuteDataSet(CommandType.Text, dataRequest.SqlCommandString, sqp);
                        }
                        else
                        {
                            return _sqlDB.ExecuteDataSet(CommandType.Text, dataRequest.SqlCommandString);
                        }
                    }
                    else
                    {
                        return _sqlDB.ExecuteDataSet(dataRequest.SqlCommandString, dataRequest.Parameters.ToArray());
                    }
                }
                else
                {
                    if (dataRequest.CommandType == CommandType.Text)
                    {
                        return _sqlDB.ExecuteDataSet(CommandType.Text, dataRequest.SqlCommandString);
                    }
                    else
                    {
                        return _sqlDB.ExecuteDataSet(dataRequest.SqlCommandString);
                    }
                }

            }
            catch (SqlException ex)
            {
                throw new DataObjectSourceException(ex.Message, ex);
            }
        }

        public virtual int ExecuteNonQuery(DataRequest dataRequest)
        {
            try
            {
//                Transaction.Current = _tranTracker.Find(dataRequest.TransactionID);
                LogExec("ExecuteNonQuery", dataRequest);
                if (Transaction.Current != null && Transaction.Current.TransactionInformation.Status != TransactionStatus.Active)
                    throw new TransactionException("Execute not allowed on inactive transaction");

                int retvalue;

                if (dataRequest.CommandType == CommandType.Text)
                {
                    retvalue = _sqlDB.ExecuteNonQuery(CommandType.Text, dataRequest.SqlCommandString);
                }
                else if (null != dataRequest.Parameters)
                {
                    if (typeof(DbParameter).IsAssignableFrom(dataRequest.Parameters[0].GetType()))
                    {
                        // If we have a list of DbParameters, build a DbCommand and execute that.

                        DbCommand cmd = _sqlDB.DbProviderFactory.CreateCommand();
                        cmd.CommandText = dataRequest.SqlCommandString;
                        cmd.CommandType = dataRequest.CommandType;

                        foreach (object p in dataRequest.Parameters)
                        {
                            cmd.Parameters.Add(p as DbParameter);
                        }
                        retvalue = _sqlDB.ExecuteNonQuery(cmd);
                    }
                    else
                    {
                        // Parameters is just list of objects.
                        retvalue = _sqlDB.ExecuteNonQuery(dataRequest.SqlCommandString, dataRequest.Parameters.ToArray());
                    }
                }
                else
                {
                    retvalue = _sqlDB.ExecuteNonQuery(dataRequest.SqlCommandString);
                }

                return retvalue;

            }
            catch (SqlException ex)
            {
                throw new DataObjectSourceException(ex.Message, ex);
            }
        }

        public virtual object ExecuteScalar(DataRequest dataRequest)
        {
            try
            {
//                Transaction.Current = _tranTracker.Find(dataRequest.TransactionID);
                LogExec("ExecuteScalar", dataRequest);
                if (Transaction.Current != null && Transaction.Current.TransactionInformation.Status != TransactionStatus.Active)
                    throw new TransactionException("Execute not allowed on inactive transaction");

                if (dataRequest.CommandType == CommandType.Text)
                {
                    return _sqlDB.ExecuteScalar(CommandType.Text, dataRequest.SqlCommandString);
                }
                else
                {
                    if (null != dataRequest.Parameters)
                    {
                        return _sqlDB.ExecuteScalar(dataRequest.SqlCommandString, dataRequest.Parameters.ToArray());
                        //return SqlHelper.ExecuteScalar(conn, dataRequest.SqlCommandString, dataRequest.Parameters.ToArray());
                    }
                    else
                    {
                        return _sqlDB.ExecuteScalar(dataRequest.SqlCommandString);
                        //return SqlHelper.ExecuteScalar(conn, dataRequest.SqlCommandString);
                    }
                }

            }
            catch (SqlException ex)
            {
                throw new DataObjectSourceException(ex.Message, ex);
            }
        }


        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            return;
        }

        #endregion

        #region Private Methods
        private static Type GetClrType(DbType dbType)
        {
            switch (dbType)
            {
                case DbType.Binary:
                    return typeof(Byte[]);
                case DbType.Boolean:
                    return typeof(Boolean);
                case DbType.Byte:
                    return typeof(Byte);
                case DbType.Currency:
                    break;
                case DbType.Date:
                    break;
                case DbType.DateTime:
                    return typeof(DateTime);
                case DbType.DateTime2:
                    break;
                case DbType.DateTimeOffset:
                    break;
                case DbType.Decimal:
                    return typeof(decimal);
                case DbType.Double:
                    return typeof(double);
                case DbType.Guid:
                    return typeof(Guid);
                case DbType.Int16:
                case DbType.Int32:
                case DbType.Int64:
                    return typeof(int);
                case DbType.Object:
                    break;
                case DbType.SByte:
                    break;
                case DbType.Single:
                    return typeof(Single);
                case DbType.AnsiString:
                case DbType.AnsiStringFixedLength:
                case DbType.String:
                case DbType.StringFixedLength:
                    return typeof(string);
                case DbType.Time:
                    break;
                case DbType.UInt16:
                    break;
                case DbType.UInt32:
                    break;
                case DbType.UInt64:
                    break;
                case DbType.VarNumeric:
                    break;
                case DbType.Xml:
                    break;
                default:
                    break;
            }
            throw new InvalidOperationException("This is not mapped");
        }

        #endregion

    }
}
